package com.rongke.web;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.rongke.commons.JsonResp;
import com.rongke.model.*;
import com.rongke.service.*;

import com.rongke.utils.DateUtils;
import com.rongke.utils.ExcelUtils;
import com.rongke.utils.StringUtil;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;


import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.CopyOnWriteArraySet;

/**
 * @version : Ver 1.0
 * @StatisticsController
 * @统计信息Controller
 */
@RestController
@RequestMapping(value = "/api/statistics")
@Transactional
@CrossOrigin(origins = "*",maxAge = 3600)
public class StatisticsController {
    private Logger log = Logger.getLogger(this.getClass());
    @Autowired
    private StatisticsService statisticsService;
    @Autowired
    private OrdersService ordersService;
    @Autowired
    private MuserService muserService;
    @Autowired
    private FlowDetailService flowDetailService;
    @Autowired
    private IncomeFlowService incomeFlowService;
    @Autowired
    private OpenPeriodService openPeriodService;

    /**
     * @param statistics
     * @return 返回值JsonResp
     * @添加统计信息
     */
    @RequestMapping(value = "/add", method = RequestMethod.POST)
    public JsonResp addStatistics(@RequestBody Statistics statistics) {
        log.debug("添加统计信息");
        statistics.setGmtDatetime(new Date());
        statisticsService.insert(statistics);
        return JsonResp.ok(statistics);
    }

    /**
     * @param statistics
     * @return 返回值JsonResp
     * @修改统计信息
     */
    @RequestMapping(value = "/update", method = RequestMethod.POST)
    public JsonResp updateStatistics(@RequestBody Statistics statistics) {
        log.debug("修改统计信息");
        statisticsService.updateById(statistics);
        return JsonResp.ok(statistics);
    }

    /**
     * @param id
     * @return 返回值JsonResp
     * @根据id查找统计信息
     */
    @RequestMapping(value = "/selectOne", method = RequestMethod.GET)
    public JsonResp selectStatistics(Long id) {
        log.debug("查找统计信息");
        Statistics statistics = statisticsService.selectById(id);
        return JsonResp.ok(statistics);
    }

    /**
     * @param statistics
     * @return 返回值JsonResp
     * @根据条件查找统计信息
     */
    @RequestMapping(value = "/selectByCondition", method = RequestMethod.POST)
    public JsonResp selectStatisticsByCondition(@RequestBody Statistics statistics) {
        log.debug("根据条件查找统计信息");
        if (statistics == null)
            return JsonResp.toFail("参数不能为空");
        List<Statistics> statisticsList = statisticsService.selectByCondition(statistics);
        return JsonResp.ok(statisticsList);
    }


    /**
     * 累加打开次数，统计打开时段
     *
     * @return
     */
    @RequestMapping(value = "/accumulateOpentimes", method = RequestMethod.GET)
    public JsonResp accumulateOpentimes() {
        log.debug("累加打开次数，统计打开时段");
        //累加当天打开次数
        Statistics st = selectCurDateStatistics();
        if (null == st) {
            st = new Statistics();
            st.setOpenTimes(1);
            st.setGmtDatetime(new Date());
            statisticsService.insert(st);
        } else {
            st.setOpenTimes(st.getOpenTimes() + 1);
            statisticsService.updateById(st);
        }
        //累加该时段访问人数
        Calendar cal = Calendar.getInstance();
        int hour = cal.get(Calendar.HOUR_OF_DAY);
        EntityWrapper<OpenPeriod> ew = new EntityWrapper<>();
        ew.eq("period", hour);
        OpenPeriod op = openPeriodService.selectOne(ew);
        op.setOpenTimes(op.getOpenTimes() + 1);
        openPeriodService.updateById(op);
        Map map = new HashMap();
        map.put("openTimes", st);
        map.put("openPeriod", op);
        return JsonResp.ok(map);
    }

    /**
     * 查询打开时间段分布 0-23
     *
     * @return
     */
    @RequestMapping(value = "/consultOpenPeriod", method = RequestMethod.GET)
    public JsonResp consultOpenPeriod() {
        log.debug("查询打开时间段分布");
        List<OpenPeriod> openPeriods = openPeriodService.selectByCondition(new OpenPeriod());
        EntityWrapper<OpenPeriod> ew = new EntityWrapper<>();
        ew.setSqlSelect("sum(open_times) sumTimes");
        BigDecimal sumTimes = convert(openPeriodService.selectObj(ew));
        Map map = new HashMap();
        map.put("openPeriods", openPeriods);
        map.put("sumTimes", sumTimes);
        return JsonResp.ok(map);
    }

    /**
     * 查询当天/月打开次数
     *
     * @return
     */
    @RequestMapping(value = "/consultOpentimes", method = RequestMethod.GET)
    public JsonResp consultOpentimes() {
        log.debug("查询当天/月打开次数");
        EntityWrapper<Statistics> ew = new EntityWrapper<>();
        EntityWrapper<Statistics> ewm = new EntityWrapper<>();
        EntityWrapper<Statistics> ewall = new EntityWrapper<>();
        //打开次数总和
        ewall.setSqlSelect("sum(open_times) as allOpentimes,id,gmt_datetime createTime");
        Integer allOpentimes = 0;
        BigDecimal opentimesAll = convert(statisticsService.selectObj(ewall));
        Map obj = statisticsService.selectMap(ewall);
        allOpentimes = opentimesAll.intValue();
/*        Map allOpentimesMap = statisticsService.selectMap(ewall);
        BigDecimal zz = (BigDecimal) allOpentimesMap.get("allOpentimes");*/
        //当天
        ew.where("to_days(gmt_datetime) = to_days(now())");
        //当月
        ewm.where("DATE_FORMAT( gmt_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
        Integer curDayOpentimes = 0;
        Integer curMonthOpentimes = 0;
        Statistics std = statisticsService.selectOne(ew);
        List<Statistics> stmList = statisticsService.selectList(ewm);
        if (null != std) {
            curDayOpentimes = std.getOpenTimes();
        }
        if (!stmList.isEmpty()) {
            for (Statistics s : stmList) {
                if (null != s.getOpenTimes())
                    curMonthOpentimes += s.getOpenTimes();
            }
        }
        Map map = new HashMap();
        map.put("obj", obj);
//        map.put("zz", zz);

        map.put("allOpentimes", allOpentimes);
        map.put("opentimesAll", opentimesAll);
//        map.put("allOpentimesMap", allOpentimesMap);
        map.put("curDayOpentimes", curDayOpentimes);
        map.put("curMonthOpentimes", curMonthOpentimes);
        return JsonResp.ok(map);
    }


    /**
     * 查询当天统计信息
     *
     * @return
     */
    @RequestMapping(value = "/selectCurDateStatistics", method = RequestMethod.GET)
    public Statistics selectCurDateStatistics() {
        log.debug("查询当天统计信息");
        EntityWrapper<Statistics> ew = new EntityWrapper<>();
        //查询当天记录，结果为null则表示没有
        ew.where("to_days(gmt_datetime) = to_days(now())");
        Statistics st = statisticsService.selectOne(ew);
        System.out.println(st);
        return st;
    }


    /**
     * 统计会员(总和月新增，分体验彩虹)
     *
     * @return
     */
    @RequestMapping(value = "/countMember", method = RequestMethod.GET)
    public JsonResp countMember() {
        log.debug("统计会员(总和月新增，分体验彩虹)");
        EntityWrapper<Muser> ew = new EntityWrapper<>();
        ew.isNotNull("user_name");
        //会员类型 1 体验 2 彩虹
        Integer memberType = 1;
        ew.eq("member_type", memberType);
        //查询总体验会员人数
        Integer memberPlainSum = muserService.selectCount(ew);
        memberType = 2;
        //查询总彩虹会员人数
        EntityWrapper<Muser> ew1 = new EntityWrapper<>();
        ew1.isNotNull("user_name")
                .eq("member_type", memberType);
        Integer memberRainbowSum = muserService.selectCount(ew1);
        //总会员人数
        Integer memberSum = memberPlainSum + memberRainbowSum;
        Integer allOrMonth = 1;
        //月查询
        String monthConsult = "DATE_FORMAT( gmt_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
        ew.where(allOrMonth == 1, monthConsult);
        memberType = 1;
        //月增体验会员人数
        Integer memberPlainMonth = muserService.selectCount(ew);
        memberType = 2;
        //月增彩虹会员人数
        ew1.where(allOrMonth == 1, monthConsult);
        Integer memberRainbowMonth = muserService.selectCount(ew1);
        //月增总会员
        Integer memberAllMonth = memberPlainMonth + memberRainbowMonth;

        //周增彩虹会员
        EntityWrapper eww = new EntityWrapper();
        memberType = 2;
        eww.isNotNull("user_name")
                .eq("member_type", memberType);
        String weekConsult = "YEARWEEK(date_format(gmt_datetime,'%Y-%m-%d')) = YEARWEEK(now())";
        eww.where(weekConsult);
        Integer memberRainbowWeek = muserService.selectCount(eww);
        Map map = new HashMap();
        map.put("memberSum", memberSum);
        map.put("memberPlainSum", memberPlainSum);
        map.put("memberRainbowSum", memberRainbowSum);
        map.put("memberAllMonth", memberAllMonth);
        map.put("memberPlainMonth", memberPlainMonth);
        map.put("memberRainbowMonth", memberRainbowMonth);

        map.put("memberRainbowWeek", memberRainbowWeek);
        return JsonResp.ok(map);
    }

    /**
     * 计算当天、当月订单总额
     *
     * @return
     */
    @RequestMapping(value = "/consultOrderMoney", method = RequestMethod.GET)
    public JsonResp consultOrderMoney() {
        log.debug("计算当天、当月订单总额");
        //总交易额
        EntityWrapper<Orders> e = new EntityWrapper<>();
        e.setSqlSelect("sum(total_price)");
        e.isNotNull("pay_datetime");
        e.notIn("order_status",Arrays.asList(9,11));//状态不为退款完成和拼团中
        BigDecimal turnoverAll = convert(ordersService.selectObj(e));
        EntityWrapper<Orders> e1 = new EntityWrapper<>();
        e1.setSqlSelect("sum(delivery_money)");
        e1.isNotNull("pay_datetime");
        e1.notIn("order_status",Arrays.asList(9,11));
        BigDecimal deliveryAll = convert(ordersService.selectObj(e1));
        EntityWrapper<Orders> e2 = new EntityWrapper<>();
        e2.setSqlSelect("sum(coupon_val)");
        e2.isNotNull("pay_datetime");
        e2.notIn("order_status",Arrays.asList(9,11));
        BigDecimal couponAll = convert(ordersService.selectObj(e2));


        BigDecimal origOrderAll = BigDecimal.ZERO;
        origOrderAll = origOrderAll.add(turnoverAll).subtract(deliveryAll).add(couponAll);
        EntityWrapper<Orders> ew = new EntityWrapper<>();
        //当日下单
        ew.where("to_days(pay_datetime) = to_days(now())");
        ew.notIn("order_status",Arrays.asList(9,11));//状态不为退款完成和拼团中
        List<Orders> odList = ordersService.selectList(ew);
        //实付金额 日
        BigDecimal curdayOrder = new BigDecimal(0);
        //原总金额 日
        BigDecimal curdayTotalOrder = new BigDecimal(0);
        if (!odList.isEmpty()) {
            for (Orders od : odList) {
                curdayOrder = curdayOrder.add(od.getTotalPrice());
                //计算还原邮费和优惠券的订单原价
                curdayTotalOrder = curdayTotalOrder.add(od.getTotalPrice()).subtract(od.getDeliveryMoney());
                if (null != od.getCouponVal()) {
                    curdayTotalOrder = curdayTotalOrder.add(BigDecimal.valueOf(od.getCouponVal()));
                }
            }
        }
        EntityWrapper<Orders> ewm = new EntityWrapper<>();
        String monthPConsult = "DATE_FORMAT( pay_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
        ewm.where(monthPConsult);
        ewm.notIn("order_status",Arrays.asList(9,11));//状态不为退款完成和拼团中
        List<Orders> odListMonth = ordersService.selectList(ewm);
        //实付金额 月
        BigDecimal curmonthOrder = new BigDecimal(0);
        //原总金额 月
        BigDecimal curmonthTotalOrder = new BigDecimal(0);
        if (!odListMonth.isEmpty()) {
            for (Orders od : odListMonth) {
                curmonthOrder = curmonthOrder.add(od.getTotalPrice());
                //计算还原邮费和优惠券的订单原价
                curmonthTotalOrder = curmonthTotalOrder.add(od.getTotalPrice()).subtract(od.getDeliveryMoney());
                if (null != od.getCouponVal()) {
                    curmonthTotalOrder = curmonthTotalOrder.add(BigDecimal.valueOf(od.getCouponVal()));
                }
            }
        }
        Map map = new HashMap();
        map.put("turnoverAll", turnoverAll);
        map.put("origOrderAll", origOrderAll);

        map.put("curdayOrder", curdayOrder);
        map.put("curmonthOrder", curmonthOrder);
        map.put("curdayTotalOrder", curdayTotalOrder);
        map.put("curmonthTotalOrder", curmonthTotalOrder);
        return JsonResp.ok(map);
    }

    /**
     * 获取当日、当月下单量
     *
     * @return
     */
    @RequestMapping(value = "/calculateOrdersAmount", method = RequestMethod.GET)
    public JsonResp calculateOrdersAmount() {
        log.debug("获取当日、当月下单量和支付量");
        EntityWrapper<Orders> ew = new EntityWrapper<>();
        //总下单量
        Integer allOrderAmount = ordersService.selectCount(ew);
        //当日下单量
        ew.where("to_days(gmt_datetime) = to_days(now())");
        Integer curdayOrderAmount = ordersService.selectCount(ew);
        ew = new EntityWrapper<>();
        ew.isNotNull("pay_datetime");
        Integer allPayOrderAmount = ordersService.selectCount(ew);
        //当日支付量
        EntityWrapper<Orders> ew1 = new EntityWrapper<>();
        ew1.where("to_days(pay_datetime) = to_days(now())");
        Integer curdayPayOrderAmount = ordersService.selectCount(ew1);
        //月查询
        String monthConsult = "DATE_FORMAT( gmt_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
        //当月下单量
        EntityWrapper<Orders> ewm = new EntityWrapper<>();
        ewm.where(monthConsult);
        Integer curmonthOrderAmount = ordersService.selectCount(ewm);

        String monthPConsult = "DATE_FORMAT( pay_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
        EntityWrapper<Orders> ewmp = new EntityWrapper<>();
        ewmp.where(monthPConsult);
        Integer curmonthPayOrderAmount = ordersService.selectCount(ewmp);

        Map openMap= (Map) consultOpentimes().getData();
        Integer dayOpen= (Integer) openMap.get("curDayOpentimes");
        Integer monthOpen= (Integer) openMap.get("curMonthOpentimes");
        Integer allOpen= (Integer) openMap.get("allOpentimes");

        Map map = new HashMap();
        //计算 下单/支付转化率、访问/支付转化率
        Double curdayConvertionRate=curdayOrderAmount==0?0:Double.valueOf(curdayPayOrderAmount)/curdayOrderAmount;
        Double curdayOpenConvertionRate=dayOpen==0?0:Double.valueOf(curdayPayOrderAmount)/dayOpen;
        Double curmonthConvertionRate=curmonthOrderAmount==0?0:Double.valueOf(curmonthPayOrderAmount)/curmonthOrderAmount;
        Double curmonthOpenConvertionRate=monthOpen==0?0:Double.valueOf(curmonthPayOrderAmount)/monthOpen;
        Double allConvertionRate=allOrderAmount==0?0:Double.valueOf(allPayOrderAmount)/allOrderAmount;
        Double allOpenConvertionRate=allOpen==0?0:Double.valueOf(allPayOrderAmount)/allOpen;

        map.put("curdayOrderAmount", curdayOrderAmount);
        map.put("curdayPayOrderAmount", curdayPayOrderAmount);
        map.put("curdayConvertionRate",curdayConvertionRate);
        map.put("curdayOpenConvertionRate",curdayOpenConvertionRate);

        map.put("curmonthOrderAmount", curmonthOrderAmount);
        map.put("curmonthPayOrderAmount", curmonthPayOrderAmount);
        map.put("curmonthConvertionRate", curmonthConvertionRate);
        map.put("curmonthOpenConvertionRate",curmonthOpenConvertionRate);

        map.put("allOrderAmount", allOrderAmount);
        map.put("allPayOrderAmount", allPayOrderAmount);
        map.put("allConvertionRate",allConvertionRate);
        map.put("allOpenConvertionRate",allOpenConvertionRate);
        return JsonResp.ok(map);
    }

    /**
     * 计算当天、当月毛利润
     *
     * @return
     */
    @RequestMapping(value = "/calculateGrossProfit")
    public JsonResp calculateGrossProfit() {
        log.debug("计算当天、当月毛利润");
        //毛利润=实付总额-返哆币总额-10%返雅币总额-提成总额-63%订单总价（商品总价）-3%实付总额的税

        //支付后未结算的返现哆币，计算毛利润用
        EntityWrapper<Orders> e3 = new EntityWrapper<>();
        e3.setSqlSelect("sum(return_duobi)");
        e3.isNotNull("pay_datetime");
        e3.notIn("order_status",Arrays.asList(9,11));
        BigDecimal allReturnDuobi = convert(ordersService.selectObj(e3));//总
        e3.where("to_days(pay_datetime) = to_days(now())");
        BigDecimal curDayReturnDuobi = convert(ordersService.selectObj(e3));//日

        EntityWrapper<Orders> e4 = new EntityWrapper<>();
        e4.setSqlSelect("sum(return_duobi)");
        e4.isNotNull("pay_datetime");
        e4.notIn("order_status",Arrays.asList(9,11));
        //月查询
        String monthPConsult = "DATE_FORMAT( pay_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
        e4.where(monthPConsult);
        BigDecimal curMonthReturnDuobi = convert(ordersService.selectObj(e4));//月
        //未结算返现雅币
        EntityWrapper<Orders> e5 = new EntityWrapper<>();
        e5.setSqlSelect("sum(return_yabi)");
        e5.isNotNull("pay_datetime");
        e5.notIn("order_status",Arrays.asList(9,11));
        BigDecimal allReturnYabi = convert(ordersService.selectObj(e5));//总
        e5.where("to_days(pay_datetime) = to_days(now())");
        BigDecimal curDayReturnYabi = convert(ordersService.selectObj(e5));//日

        EntityWrapper<Orders> e6 = new EntityWrapper<>();
        e6.setSqlSelect("sum(return_yabi)");
        e6.isNotNull("pay_datetime");
        e6.notIn("order_status",Arrays.asList(9,11));
        e6.where(monthPConsult);
        BigDecimal curMonthReturnYabi = convert(ordersService.selectObj(e6));//日



        //计算日毛利润
        Map payTotalObj = (Map) consultOrderMoney().getData();


        BigDecimal daypayTotal = (BigDecimal) payTotalObj.get("curdayOrder");
        BigDecimal curmonthOrder = (BigDecimal) payTotalObj.get("curmonthOrder");
        BigDecimal curdayTotalOrder = (BigDecimal) payTotalObj.get("curdayTotalOrder");
        BigDecimal curmonthTotalOrder = (BigDecimal) payTotalObj.get("curmonthTotalOrder");

        Map returnObj = (Map) consultReturnSum().getData();

        BigDecimal dayReturnDuobi =curDayReturnDuobi;
        BigDecimal dayReturnYabi = curDayReturnYabi;
        BigDecimal curmonthReturnDuobiSum =curMonthReturnDuobi;
        BigDecimal curmonthReturnYabiSum = curMonthReturnYabi;

        Map incomeObj = (Map) consultIncome().getData();

        BigDecimal dayIncome = (BigDecimal) incomeObj.get("dayIncome");
        BigDecimal monthIncome = (BigDecimal) incomeObj.get("monthIncome");

        //总毛利润
//        BigDecimal allGrossProfit=

        //日毛利润
        BigDecimal dayGrossProfit = daypayTotal.subtract(dayReturnDuobi)
                .subtract(dayReturnYabi.multiply(new BigDecimal(0.1)))
                .subtract(dayIncome)
                .subtract(curdayTotalOrder.multiply(new BigDecimal(0.63)))
                .subtract(daypayTotal.multiply(new BigDecimal(0.03)));

        //月毛利润
        BigDecimal monthGrossProfit = curmonthOrder.subtract(curmonthReturnDuobiSum)
                .subtract(curmonthReturnYabiSum.multiply(new BigDecimal(0.1)))
                .subtract(monthIncome)
                .subtract(curmonthTotalOrder.multiply(new BigDecimal(0.63)))
                .subtract(curmonthOrder.multiply(new BigDecimal(0.03)));
        Map map = new HashMap();
        map.put("dayGrossProfit", dayGrossProfit);
        map.put("monthGrossProfit", monthGrossProfit);
        return JsonResp.ok(map);
    }

    /**
     * 查询计算当日、当月返现金额
     *
     * @return
     */
    @RequestMapping(value = "/consultReturnSum", method = RequestMethod.GET)
    public JsonResp consultReturnSum() {
        log.debug("查询计算当日、当月返现金额");
        EntityWrapper<FlowDetail> ewy = new EntityWrapper<>();
        ewy.setSqlSelect("sum(money)");
        //5 哆币返现，6 雅币返现
        ewy.eq("flow_type", 6);
        ewy.where("to_days(gmt_datetime) = to_days(now())");
        //计算当天返现雅币总额
        BigDecimal curdayReturnYabiSum = convert(flowDetailService.selectObj(ewy));
        EntityWrapper<FlowDetail> ewd = new EntityWrapper<>();
        ewd.setSqlSelect("sum(money)");
        //5 哆币返现，6 雅币返现
        ewd.eq("flow_type", 5);
        ewd.where("to_days(gmt_datetime) = to_days(now())");
        //计算当天返现哆币总额
        BigDecimal curdayReturnDuobiSum = convert(flowDetailService.selectObj(ewd));


        EntityWrapper<FlowDetail> ewm = new EntityWrapper<>();
        ewm.setSqlSelect("sum(money)");
        ewm.eq("flow_type", 5);
        //月查询
        String monthConsult = "DATE_FORMAT( gmt_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
        ewm.where(monthConsult);
//        List<FlowDetail> fdListMonthD = flowDetailService.selectList(ewm);
        //计算当月返现哆币总额
        BigDecimal curmonthReturnDuobiSum = convert(flowDetailService.selectObj(ewm));
        EntityWrapper<FlowDetail> ewmy = new EntityWrapper<>();
        ewmy.setSqlSelect("sum(money)");
        ewmy.eq("flow_type", 6);
        ewmy.where(monthConsult);
//        List<FlowDetail> fdListMonthY = flowDetailService.selectList(ewmy);
        //计算当月返现哆币总额
        BigDecimal curmonthReturnYabiSum = convert(flowDetailService.selectObj(ewmy));
        Map map = new HashMap();
        map.put("curdayReturnDuobiSum", curdayReturnDuobiSum);
        map.put("curdayReturnYabiSum", curdayReturnYabiSum);
        map.put("curmonthReturnDuobiSum", curmonthReturnDuobiSum);
        map.put("curmonthReturnYabiSum", curmonthReturnYabiSum);
        return JsonResp.ok(map);
    }


    /**
     * 查询计算日、月提成总额
     *
     * @return
     */
    @RequestMapping(value = "/consultIncome", method = RequestMethod.GET)
    public JsonResp consultIncome() {
        log.debug("查询计算日、月提成总额");
        EntityWrapper<IncomeFlow> ew = new EntityWrapper<>();
        ew.setSqlSelect("sum(income)");
        ew.eq("status",1);
        ew.where("to_days(gmt_datetime) = to_days(now())");
        BigDecimal dayIncome = convert(incomeFlowService.selectObj(ew));

        EntityWrapper<IncomeFlow> ewm = new EntityWrapper<>();
        String monthConsult = "DATE_FORMAT( gmt_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
        ewm.setSqlSelect("sum(income)");
        ewm.eq("status",1);
        ewm.where(monthConsult);
        BigDecimal monthIncome = convert(incomeFlowService.selectObj(ewm));
        Map map = new HashMap();
        map.put("dayIncome", dayIncome); //日提成
        map.put("monthIncome", monthIncome); //月提成
        return JsonResp.ok(map);
    }

    /**
     * 查询订单年月统计数据
     *
     * @param year
     * @return
     */
    @RequestMapping(value = "/consultOrderStatistics", method = RequestMethod.GET)
    public JsonResp consultOrderStatistics(String year) {
        log.debug("查询订单年月统计数据");

        //每月数据
        List<Map> monthData = new ArrayList();
        for (int i = 0; i < 12; i++) {
            monthData.add(new HashMap<>());
        }
        BigDecimal zero = BigDecimal.ZERO;


        //统计每月订单交易额和支付人数
        EntityWrapper<Orders> ew = new EntityWrapper<>();
        ew.setSqlSelect("sum(total_price) monthPay",
                "sum(delivery_money) monthDelivery",
                "sum(coupon_val) monthCoupon",
                "count(*) monthPayCount",
                "MONTH (pay_datetime) monthFlag");
        if (StringUtil.isNotEmpty(year)) {
            ew.where("YEAR (pay_datetime)={0}", year);
        }
        ew.groupBy("monthFlag");
        List<Map<String, Object>> monthPayList = ordersService.selectMaps(ew);
        monthPayList.forEach(e -> {
            Integer monthFlag = (Integer) e.get("monthFlag");
            Arrays.asList("monthPay", "monthDelivery", "monthCoupon", "monthPayCount").forEach(s -> {
                if (null == e.get(s)) {
                    e.put(s, zero);
                }
            });
            if (null != monthFlag) {
                Map map = monthData.get(monthFlag - 1);
                map.put("monthPayOrder", e);
            }
        });

        //月下单人数
        ew = new EntityWrapper<>();
        ew.setSqlSelect("count(*) monthOrderCount",
                "MONTH (gmt_datetime) monthFlag");
        if (StringUtil.isNotEmpty(year)) {
            ew.where("YEAR (gmt_datetime)={0}", year);
        }
        ew.groupBy("monthFlag");
        List<Map<String, Object>> monthOrderList = ordersService.selectMaps(ew);
        monthOrderList.forEach(e -> {
            Integer monthFlag = (Integer) e.get("monthFlag");
            if (null == e.get("monthOrderCount")) {
                e.put("monthOrderCount", zero);
            }
            if (null != monthFlag) {
                Map map = monthData.get(monthFlag - 1);
                map.put("monthOrderCount", e);
            }
        });
        return JsonResp.ok(monthData);
    }


    /**
     * 查询某基准日期的某阶段（ 1日、2周、3月、4年）统计数据
     *
     * @param queryDate   基准日期
     * @param queryPeriod 1 日、2 周、3 月、4 年
     * @return
     */
    @RequestMapping(value = "/consultPeriodStatistics", method = RequestMethod.GET)
    public JsonResp consultPeriodStatistics(String queryDate, Integer queryPeriod) {
        log.debug("查询某基准日期的某阶段（ 1 日、2 周、3 月、4 年）统计数据");
        if (StringUtil.isEmpty(queryDate) || StringUtil.isEmpty(queryPeriod.toString()))
            return JsonResp.toFail("参数不能为空");
        //时间字段
        String columnGmtTime = "gmt_datetime";
        String columnPayTime = "pay_datetime";
        //实际要用的查询字段
        String consultColumn = "";


        String dayWhere = "date(\'" + queryDate + "\')=date(" + columnGmtTime + ")";
        String weekWhere = "yearweek(\'" + queryDate + "\', 1) = yearweek(" + columnGmtTime + ", 1)";
        String monthWhere = " DATE_FORMAT(\'" + queryDate + "\' , '%Y%m')=DATE_FORMAT(" + columnGmtTime + ", '%Y%m' )";
        String yearWhere = "YEAR (\'" + queryDate + "\') = YEAR (" + columnGmtTime + ")";
        String dayPWhere = "date(\'" + queryDate + "\')=date(" + columnPayTime + ")";
        String weekPWhere = "yearweek(\'" + queryDate + "\', 1) = yearweek(" + columnPayTime + ", 1)";
        String monthPWhere = " DATE_FORMAT(\'" + queryDate + "\' , '%Y%m')=DATE_FORMAT(" + columnPayTime + ", '%Y%m' )";
        String yearPWhere = "YEAR (\'" + queryDate + "\') = YEAR (" + columnPayTime + ")";

        String consultPWhereSQL = "";
        String consultGmtWhereSQL = "";
        //queryPeriod  1 日、2 周、3 月、4 年
        if (queryPeriod == 1) {
            consultPWhereSQL = dayPWhere;
            consultGmtWhereSQL = dayWhere;
        } else if (queryPeriod == 2) {
            consultPWhereSQL = weekPWhere;
            consultGmtWhereSQL = weekWhere;
        } else if (queryPeriod == 3) {
            consultPWhereSQL = monthPWhere;
            consultGmtWhereSQL = monthWhere;
        } else if (queryPeriod == 4) {
            consultPWhereSQL = yearPWhere;
            consultGmtWhereSQL = yearWhere;
        }
        Map resultMap = new HashMap();

        //交易总额，支付总数
        EntityWrapper<Orders> ew = new EntityWrapper<>();
        ew.setSqlSelect("IFNULL(sum(total_price),0) periodTurnover",
                "count(*) periodPayCount");
        ew.where(consultPWhereSQL);
        Map payMap = ordersService.selectMap(ew);

        //下单数
        EntityWrapper<Orders> ew1 = new EntityWrapper<>();
        ew1.setSqlSelect("count(*) periodOrderCount");
        ew1.where(consultGmtWhereSQL);
        Map orderMap = ordersService.selectMap(ew1);

        //返现哆币雅币
        EntityWrapper<FlowDetail> ewd = new EntityWrapper<>();
        ewd.setSqlSelect("sum(money)");
        //5 哆币返现，6 雅币返现
        ewd.eq("flow_type", 5);
        ewd.where(consultGmtWhereSQL);
        //返现哆币总额
        BigDecimal periodReturnDuobiSum = convert(flowDetailService.selectObj(ewd));
        EntityWrapper<FlowDetail> ewy = new EntityWrapper<>();
        ewy.setSqlSelect("sum(money)");
        ewy.eq("flow_type", 6);
        ewy.where(consultGmtWhereSQL);
        //返现雅币总额
        BigDecimal periodReturnYabiSum = convert(flowDetailService.selectObj(ewy));

        //新增会员
        EntityWrapper<Muser> ew2 = new EntityWrapper<>();
        ew2.isNotNull("user_name");
        //会员类型 1 体验 2 彩虹
        ew2.eq("member_type", 1);
        ew2.where(consultGmtWhereSQL);
        //新增体验
        Integer periodMemberPlain = muserService.selectCount(ew2);
        EntityWrapper<Muser> ew3 = new EntityWrapper<>();
        ew3.isNotNull("user_name");
        ew3.eq("member_type", 2);
        ew3.where(consultGmtWhereSQL);
        //新增彩虹
        Integer periodMemberRainbow = muserService.selectCount(ew3);
        //新增总会员
        Integer periodMemberSum = periodMemberPlain + periodMemberRainbow;

        //访问人次
        EntityWrapper<Statistics> ewall = new EntityWrapper<>();
        ewall.setSqlSelect("sum(open_times) as allOpentimes");
        ewall.where(consultGmtWhereSQL);
        BigDecimal periodOpentimes = convert(statisticsService.selectObj(ewall));


        resultMap.put("payMap", payMap);//key：periodTurnover、periodPayCount
        resultMap.put("orderMap", orderMap);//key: periodOrderCount
        resultMap.put("periodReturnDuobiSum", periodReturnDuobiSum);
        resultMap.put("periodReturnYabiSum", periodReturnYabiSum);
        resultMap.put("periodMemberPlain", periodMemberPlain);
        resultMap.put("periodMemberRainbow", periodMemberRainbow);
        resultMap.put("periodMemberSum", periodMemberSum);
        resultMap.put("periodOpentimes", periodOpentimes);
        return JsonResp.ok(resultMap);
    }

    /**
     * 生成每月销售报总表
     * @return
     */
    @RequestMapping(value = "/getMonthSaleReport", method = RequestMethod.GET)
    public JsonResp getMonthSaleReport(String queryDate,HttpServletResponse response) throws Exception {
        log.debug("生成每月销售报总表");
        Map condition = new HashMap();
        queryDate=decorateDateStr(queryDate);
        condition.put("queryDate",queryDate);
        List<MonthSaleReport> reports = statisticsService.getMonthSaleReport(condition);
/*        String filename="月销售报总表";
        List<String> headList = Arrays.asList("产品名称", "商品标价", "销售数量", "消费订单数",
                "男用户", "女用户", "20岁以下", "20-30岁", "31-40岁", "41-50岁", "50岁以上");
        List<List<String>> dataList = new ArrayList<>();
        reports.forEach(e->{
            List<String> dataChildren=new ArrayList<>();
            dataChildren.add(e.getGoodsName());
            dataChildren.add(e.getPrice().toString());
            dataChildren.add(e.getSaleCount().toString());
            dataChildren.add(e.getOrderCount().toString());
            dataChildren.add(e.getMale().toString());
            dataChildren.add(e.getFemale().toString());
            dataChildren.add(e.getLtTwenty().toString());
            dataChildren.add(e.getTwenty2thirty().toString());
            dataChildren.add(e.getThirty2forty().toString());
            dataChildren.add(e.getForty2fifty().toString());
            dataChildren.add(e.getGtFifty().toString());
            dataList.add(dataChildren);
        });
        ExcelUtils.exportData(filename,headList,dataList,response);*/
        return JsonResp.ok(reports);
    }

    //某年月转日期
    private String decorateDateStr(String queryDate) {
        if(StringUtil.isEmpty(queryDate)){
            queryDate= DateUtils.nowDate();
        }else{
            queryDate=queryDate+"-1";
        }
        return queryDate;
    }


    /**
     * 生成月各区域消费占比表
     * @return
     */
    @RequestMapping(value = "/getMonthCitySaleRateReport", method = RequestMethod.GET)
    public JsonResp getMonthCitySaleRateReport(String queryDate,HttpServletResponse response) throws Exception {
        log.debug("生成月各区域消费占比表");
        queryDate=decorateDateStr(queryDate);
        Map condition = new HashMap();
        condition.put("queryDate",queryDate);
        List<MonthSaleReport> reports = statisticsService.getMonthCitySaleRateReport(condition);
        return JsonResp.ok(reports);
    }





    /**
     * 生成月区域销售表
     * @return
     */
    @RequestMapping(value = "/getMonthCitySaleReport", method = RequestMethod.GET)
    public JsonResp getMonthCitySaleReport(String queryDate,HttpServletResponse response) throws Exception {
        log.debug("生成月区域销售表");
        queryDate=decorateDateStr(queryDate);
        Map condition = new HashMap();
        condition.put("queryDate",queryDate);
        List<MonthSaleReport> reports = statisticsService.getMonthCitySaleReport(condition);
/*        String filename="月区域销售表";
        List<String> headList = Arrays.asList("产品名称", "商品标价", "销售数量", "购买者区域",
                "男用户", "女用户", "20岁以下", "20-30岁", "31-40岁", "41-50岁", "50岁以上");
        List<List<String>> dataList = new ArrayList<>();
        reports.forEach(e->{
            List<String> dataChildren=new ArrayList<>();
            dataChildren.add(e.getGoodsName());
            dataChildren.add(e.getPrice().toString());
            dataChildren.add(e.getSaleCount().toString());
            dataChildren.add(e.getCity().toString());
            dataChildren.add(e.getMale().toString());
            dataChildren.add(e.getFemale().toString());
            dataChildren.add(e.getLtTwenty().toString());
            dataChildren.add(e.getTwenty2thirty().toString());
            dataChildren.add(e.getThirty2forty().toString());
            dataChildren.add(e.getForty2fifty().toString());
            dataChildren.add(e.getGtFifty().toString());
            dataList.add(dataChildren);
        });
        ExcelUtils.exportData(filename,headList,dataList,response);*/
        return JsonResp.ok(reports);
    }

    //转化为BigDecimal返回，null返回ZERO
    public BigDecimal convert(Object obj) {
        BigDecimal result = BigDecimal.ZERO;
        if (null != obj) {
            result = (BigDecimal) obj;
        }
        return result;
    }

    public List<String> convert2StrList(List list) {
        String z = list.toString().substring(1, list.toString().length() - 1);
        String[] zzz = z.split(",");
        List<String> mm = Arrays.asList(zzz);
        return mm;
    }

    public static void main(String[] args) {
        BigDecimal curdayReturnSum = BigDecimal.ZERO;
        BigDecimal curdayReturnSum1 = new BigDecimal(0);
        for (int i = 0; i < 5; i++) {
            curdayReturnSum1 = curdayReturnSum1.add(new BigDecimal(3));
        }
        System.out.println(curdayReturnSum);
        System.out.println(curdayReturnSum1);

        //时间字段
        StringBuilder columnGmtTime = new StringBuilder("gmt_datetime");
        StringBuilder columnPayTime = new StringBuilder("pay_datetime");
        //实际要用的查询字段
        StringBuilder consultColumn = new StringBuilder();


        StringBuilder zz = new StringBuilder("");
        StringBuilder yearWhere = zz.append("YEAR ('")
                .append("') = YEAR (")
                .append(consultColumn)
                .append(")");

        consultColumn.append(columnPayTime);
        System.out.println("yearWhere:" + yearWhere);

        Calendar cal = Calendar.getInstance();
        cal.setTime(new Date());
        cal.add(Calendar.MINUTE, -15);
        System.out.println(cal.getTime());
        List headList = new ArrayList<>();
        headList.add(1);
        headList.add("测试2");
        headList.add("测试3");
        headList.add("测试4");

        List<List<String>> dataList = new ArrayList<>();
        for (int i = 0; i < 60000; i++) {
            List<String> list = new ArrayList<>();
            list.add("数据1" + i);
            list.add("数据2" + i);
            list.add("数据3" + i);
            list.add("数据4" + i);
            dataList.add(list);
        }
        String z = headList.toString().substring(1, headList.toString().length() - 1);
        String[] zzz = z.split(",");
        List<String> mm = Arrays.asList(zzz);
        System.out.println(mm);
        System.out.println(DateUtils.nowDate());
    }
}
